import pymysql


class StudentCurrmService:
    def update(self, inp, studentId, currmId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "update student_currm set score = %s where student_id = %s and currm_id = %s"
        data = (inp['score'], studentId, currmId)
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'

    def delete(self, studentId, currmId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "delete from student_currm where student_id = %s and currm_id = %s"
        data = (studentId, currmId)
        sql = sql % data
        print(sql)
        try:
            cursor.execute(sql)
        except:
            return 'sql错误'
        sqlConnect.commit()
        cursor.close()
        sqlConnect.close()
        return True

    def create(self, inp, studentId, currmId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "insert into student_currm (student_id,currm_id,score) values (%s,%s,%s)"
        data = (studentId, currmId, inp['score'])
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'
